package repository

import (
	"context"
	"database/sql"

	"gitee.com/hp-huiw/family-expense-api/internal/db"
	dbmodel "gitee.com/hp-huiw/family-expense-api/internal/db/model"
	jet "github.com/go-jet/jet/v2/mysql"
)

type AccountGroupOwner struct {
	ID       string `db:"id"`
	Username string `db:"username"`
	FullName string `db:"full_name"`
}

type AccountGroupUser struct {
	ID             string `db:"id"`
	Username       string `db:"username"`
	FullName       string `db:"full_name"`
	AccountGroupID string `db:"account_group_id"`
	AvatarFilename string `db:"avatar_filename"`
}

type AccountGroupUserRepository interface {
	QueryCreatorID(ctx context.Context, accountGroupID string) (string, error)
	QueryIDByCreator(ctx context.Context, userID string) (string, error)
	QueryIDs(ctx context.Context, userID string) ([]string, error)
	QueryAccountGroupOwner(ctx context.Context, accountGroupID string) (*AccountGroupOwner, error)
	QueryCreators(ctx context.Context, accountGroupIDs []string) ([]AccountGroupUser, error)
	Count(ctx context.Context, accountGroupID string) (int64, error)
	ExistByAccountGroupAndUser(ctx context.Context, accountGroupID string, userID string) (bool, error)
	ExistByAccountGroupAndUserAndIsAccountGroupCreator(ctx context.Context, accountGroupID string, userID string) (bool, error)
	InsertWithTx(ctx context.Context, tx *sql.Tx, model *dbmodel.AccountGroupUser) error
	DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string, userID string) error
	DeleteNonCreatorWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string, userID string) error
}

// NewAccountGroupUserRepository creates a new AccountGroupUserRepository.
func NewAccountGroupUserRepository(db *sql.DB) AccountGroupUserRepository {
	return &accountGroupUserRepository{db}
}

// QueryCreatorID queries creator id by account group id.
func (r *accountGroupUserRepository) QueryCreatorID(ctx context.Context, accountGroupID string) (string, error) {
	var query struct {
		UserID string `db:"user_id"`
	}

	stmt := jet.
		SELECT(
			tbGroupUser.UserID.AS("user_id"),
		).
		FROM(
			tbGroupUser,
		).
		WHERE(
			tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return "", err
	}

	return query.UserID, nil
}

// QueryIDByCreator queries user's own account group id.
func (r *accountGroupUserRepository) QueryIDByCreator(ctx context.Context, userID string) (string, error) {
	var query struct {
		AccountGroupID string `db:"account_group_id"`
	}

	stmt := jet.
		SELECT(
			tbGroupUser.AccountGroupID.AS("account_group_id"),
		).
		FROM(
			tbGroupUser,
		).
		WHERE(
			tbGroupUser.UserID.EQ(jet.String(userID)).
				AND(tbGroupUser.IsAccountGroupCreator.IS_TRUE()),
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return "", err
	}

	return query.AccountGroupID, nil
}

// QueryIDs queries user's account group ids.
func (r *accountGroupUserRepository) QueryIDs(ctx context.Context, userID string) ([]string, error) {
	ids := []string{}

	stmt := jet.
		SELECT(
			tbGroupUser.AccountGroupID,
		).
		FROM(
			tbGroupUser,
		).
		WHERE(
			tbGroupUser.UserID.EQ(jet.String(userID)),
		)

	if err := stmt.QueryContext(ctx, r.db, &ids); err != nil {
		return nil, err
	}

	return ids, nil
}

// QueryAccountGroupOwner queries account group owner info by account group id.
func (r *accountGroupUserRepository) QueryAccountGroupOwner(ctx context.Context, accountGroupID string) (*AccountGroupOwner, error) {
	var owner struct {
		ID       string `db:"id"`
		Username string `db:"username"`
		FullName string `db:"full_name"`
	}

	stmt := jet.
		SELECT(
			tbGroupUser.UserID.AS("id"),
			tbUser.Username.AS("username"),
			tbUser.FullName.AS("full_name"),
		).
		FROM(
			tbGroupUser.
				INNER_JOIN(tbUser, tbUser.ID.EQ(tbGroupUser.UserID).
					AND(tbUser.AccountGroupID.EQ(tbGroupUser.AccountGroupID))),
		).
		WHERE(
			tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbGroupUser.IsAccountGroupCreator.IS_TRUE()),
		)

	if err := stmt.QueryContext(ctx, r.db, &owner); err != nil {
		return nil, err
	}

	return (*AccountGroupOwner)(&owner), nil
}

// QueryCreators queries account group creators.
func (r *accountGroupUserRepository) QueryCreators(ctx context.Context, accountGroupIDs []string) ([]AccountGroupUser, error) {
	var query []struct {
		ID             string `db:"id"`
		Username       string `db:"username"`
		FullName       string `db:"full_name"`
		AccountGroupID string `db:"account_group_id"`
		AvatarFilename string `db:"avatar_filename"`
	}

	if len(accountGroupIDs) > 0 {
		accountGroupIDsExpression := db.SliceExpression(accountGroupIDs)

		stmt := jet.
			SELECT(
				tbGroupUser.UserID.AS("id"),
				tbGroupUser.AccountGroupID.AS("account_group_id"),
				tbUser.Username.AS("username"),
				tbUser.FullName.AS("full_name"),
				tbUser.AvatarFilename.AS("avatar_filename"),
			).
			FROM(
				tbGroupUser.
					INNER_JOIN(tbUser, tbUser.ID.EQ(tbGroupUser.UserID)),
			).
			WHERE(
				tbGroupUser.AccountGroupID.IN(accountGroupIDsExpression...).
					AND(tbGroupUser.IsAccountGroupCreator.IS_TRUE()),
			).
			ORDER_BY(
				tbUser.FullName.ASC(),
			)

		if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
			return nil, err
		}
	}

	result := make([]AccountGroupUser, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// Count counts the account group user by accountGroupID.
func (r *accountGroupUserRepository) Count(ctx context.Context, accountGroupID string) (int64, error) {
	return db.Count(ctx, r.db, tbGroupUser, tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)))
}

// ExistByAccountGroupAndUser checks if user exists in account group.
func (r *accountGroupUserRepository) ExistByAccountGroupAndUser(ctx context.Context, accountGroupID string, userID string) (bool, error) {
	condition := tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)).
		AND(tbGroupUser.UserID.EQ(jet.String(userID)))

	return db.Exists(ctx, r.db, tbGroupUser, condition)
}

// ExistByAccountGroupAndUserAndIsAccountGroupCreator checks if user exists in account group and is account group creator.
func (r *accountGroupUserRepository) ExistByAccountGroupAndUserAndIsAccountGroupCreator(ctx context.Context, accountGroupID string, userID string) (bool, error) {
	condition := tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)).
		AND(tbGroupUser.UserID.EQ(jet.String(userID))).
		AND(tbGroupUser.IsAccountGroupCreator.IS_TRUE())

	return db.Exists(ctx, r.db, tbGroupUser, condition)
}

// InsertWithTx inserts account group user with transaction.
func (*accountGroupUserRepository) InsertWithTx(ctx context.Context, tx *sql.Tx, model *dbmodel.AccountGroupUser) error {
	stmt := tbGroupUser.
		INSERT().
		MODEL(model)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

// DeleteWithTx deletes user from account group by accountGroupID or userID.
func (*accountGroupUserRepository) DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string, userID string) error {
	stmt := tbGroupUser.
		DELETE().
		WHERE(
			tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)).
				OR(tbGroupUser.UserID.EQ(jet.String(userID))),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

// DeleteNonCreatorWithTx deletes user from account group who is not creator.
func (*accountGroupUserRepository) DeleteNonCreatorWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string, userID string) error {
	stmt := tbGroupUser.
		DELETE().
		WHERE(
			tbGroupUser.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbGroupUser.UserID.EQ(jet.String(userID))).
				AND(tbGroupUser.IsAccountGroupCreator.IS_FALSE()),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

type accountGroupUserRepository struct {
	db *sql.DB
}
